package lazy2.tool.beangenerator;

import java.io.*;
import java.sql.*;
import java.util.*;
import lazy2.util.StringUtil;

/**
 * 使用範例：<br/>
 * BeanGenerator.genBean(DriverName.DRIVER_NAME_JTDS_DRIVER, new JdbcUrl(DBType.MSSQLServer, "192.168.123.33", "cos_ok"), "sa", "@satku74123698@", "C:/", "tw.com.ebti");
 * @author Tim
 */
public class BeanGenerator {
	private final static StringBuilder getStringBuilder(StringBuilder stringBuilder) {
		return stringBuilder!=null?stringBuilder:new StringBuilder();
	}
	private static StringBuilder appendSrcImports(StringBuilder src, Set<String> imports) {
		src = getStringBuilder(src);
		for(String s:imports) {
			src.append("import ").append(s).append(";\n");
		}
		return src;
	}
	private static StringBuilder appendSrcProperties(StringBuilder src, List<ColumnDefinition> columns, boolean isJpa) {
		src = getStringBuilder(src);
		for(ColumnDefinition column:columns) {
			if(isJpa) {
				if(column.isPrimaryKey()) {
					src.append("\t@Id\n");
					if(column.isIdentity()) src.append("\t@GeneratedValue(strategy=GenerationType.IDENTITY)\n");
				}
				if(!column.getColumnName().equals(column.getPropertyName())) {
					src.append("\t@Column(name=\"").append(column.getColumnName()).append("\")\n");
				}
			}
			src.append("\tprivate ").append(column.getPropertyClassName()).append(" ").append(column.getPropertyName()).append(";\n");
		}
		return src;
	}
	static StringBuilder appendSrcSql(StringBuilder src, String tableName, List<ColumnDefinition> columns) {
		src = getStringBuilder(src);
		StringBuilder insert = new StringBuilder("\tpublic static final String INSERT_").append(tableName.toUpperCase()).append(" = \"insert into ").append(tableName).append("(");
		StringBuilder values = new StringBuilder(" values(");
		StringBuilder update = new StringBuilder("\tpublic static final String UPDATE_").append(tableName.toUpperCase()).append(" = \"update ").append(tableName).append(" set ");
		StringBuilder where = null;
		
		for(int i=0;i<columns.size();i++) {
			ColumnDefinition column = columns.get(i);
			insert.append(column.getColumnName());
			values.append("?");
			if(column.isPrimaryKey()) {
				if(where==null) {
					where = new StringBuilder(" where ").append(column.getColumnName()).append("=?");
				} else {
					where.append(" and ").append(column.getColumnName()).append("=?");
				}
			} else {
				update.append(column.getColumnName()).append("=?");
				if(i<columns.size()-1) {
					update.append(", ");
				} else {
					if(where==null) {
						update.append("\";\n");
					} else {
						where.append("\";\n");
					}
				}
			}
			if(i<columns.size()-1) {
				insert.append(", ");
				values.append(", ");
			} else {
				insert.append(")");
				values.append(")\";\n");
			}
		}
		src.append("\tpublic static final String SELECT_ALL_").append(tableName.toUpperCase()).append(" = \"select * from ").append(tableName).append("\";\n");
		src.append(insert);
		src.append(values);
		src.append(update);
		src.append(where);
		return src;
	}
	private static StringBuilder appendSrcSetterGetterMethods(StringBuilder src, List<ColumnDefinition> columns, boolean isJpa) {
		src = getStringBuilder(src);
		for(ColumnDefinition column:columns) {
			boolean deffColAndProp = !column.getColumnName().equals(column.getPropertyName());
			if(!isJpa && column.isPrimaryKey()) src.append("\t@PK\n");
			if(!isJpa && deffColAndProp) {
				src.append("\t@Column(\"").append(column.getColumnName()).append("\")\n"); 
			}
			src.append("\tpublic void ").append(StringUtil.toSetter(column.getPropertyName())).append("(").append(column.getPropertyClassName()).append(" ").append(column.getPropertyName()).append(") {\n");
			src.append("\t\t").append("this.").append(column.getPropertyName()).append(" = ").append(column.getPropertyName()).append(";\n");
			src.append("\t}\n");
			if(!isJpa && column.isPrimaryKey()) src.append("\t@PK\n");
			if(!isJpa && deffColAndProp) {
				src.append("\t@Column(\"").append(column.getColumnName()).append("\")\n"); 
			}
			src.append("\tpublic ").append(column.getPropertyClassName()).append(" ").append(StringUtil.toGetter(column.getPropertyName())).append("() {\n");
			src.append("\t\treturn this.").append(column.getPropertyName()).append(";\n");
			src.append("\t}\n\n");
		}
		return src;
	}
	
	private static void close(ResultSet resultSet, Statement statement, Connection connection, Writer writer) {
		if (resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(writer!=null) {
			try {
				writer.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static Connection getConnection(String driverName, String jdbcUrl, String userName, String password) throws ClassNotFoundException, SQLException {
		Class.forName(driverName);
		return DriverManager.getConnection(jdbcUrl, userName, password);
	}
	
	private static void _genBean(DatabaseConnection databaseConnection, String fileRoot, String packageName, String tableName, boolean isJpa) throws SQLException, IOException, ClassNotFoundException {
		if(databaseConnection==null || databaseConnection.isClosed()
		|| StringUtil.isEmpty(fileRoot)
		|| StringUtil.isEmpty(packageName)
		|| StringUtil.isEmpty(tableName))
			return;

		PreparedStatement stmt = null;
		ResultSet rs = null;
		BufferedWriter out = null;
		try {
			StringBuilder sb = new StringBuilder();
			if(!fileRoot.endsWith("/") && !fileRoot.endsWith("\\")) {
				fileRoot += "/";
			}
			final String ROOT_PATH = sb.append(fileRoot).append(packageName.replaceAll("\\.", "/")).toString();
			File root = new File(ROOT_PATH);
			if(!root.exists()) root.mkdirs();

			ResultSetMetaData metaData = databaseConnection.getTableMetaData(tableName);
			Set<String> importSrc = new HashSet<String>();
			List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
			ColumnDefinition column;
			String propertyClassName, columnName;
			PrimaryKeyInfo pkInfo;
			for(int i=1,len=metaData.getColumnCount();i<=len;i++) {
				columnName = metaData.getColumnName(i);
				propertyClassName = metaData.getColumnClassName(i);

				if(!propertyClassName.startsWith("java.lang")) {
					if("java.sql.Timestamp".equals(propertyClassName)
					|| "java.sql.Date".equals(propertyClassName)
					|| "java.sql.Time".equals(propertyClassName)) {
						propertyClassName = "java.util.Date";
						importSrc.add("java.util.Date");
					} else if("java.math.BigDecimal".equals(propertyClassName)) {
						propertyClassName = "java.lang.Double";
					} else if("java.sql.Clob".equals(propertyClassName)) {
						propertyClassName = "java.lang.String";
					} else {}
				}
				column = new ColumnDefinition();
				column.setColumnName(columnName);
				if((pkInfo=databaseConnection.isPrimaryKey(tableName, columnName))!=null) {
					column.setPrimaryKey(true);
					column.setIdentity(pkInfo.identity);
				}
				column.setPropertyClassName(Class.forName(propertyClassName).getSimpleName());
				columns.add(column);
			}
			String className = StringUtil.toUpperCase(ColumnDefinition.dbStringToJavaString(tableName),0);
			out = new BufferedWriter(new FileWriter(new StringBuilder(ROOT_PATH).append("/").append(className).append(".java").toString()));
			sb.setLength(0);
			sb.append("package ").append(packageName).append(";\n");
			sb.append("\n");
			if(isJpa) {
				importSrc.add("javax.persistence.*");
			} else {
				importSrc.add("ebti.db.annotation.*");
			}
			appendSrcImports(sb, importSrc);
			sb.append("\n");
			if(isJpa) {
				sb.append("@Entity\n");
			}
			if(!tableName.equals(className)) {
				sb.append("@Table(");
				if(isJpa) {
					sb.append("name=");
				}
				sb.append("\"").append(tableName).append("\")\n");
			}
			sb.append("public class ").append(className).append(" {\n");
			appendSrcProperties(sb, columns, isJpa);
			sb.append("\n");
			//getSrcSql(src, tableName, columns);
			//src.append("\n");
			appendSrcSetterGetterMethods(sb, columns, isJpa);
			sb.append("}");
			out.write(sb.toString());
		} finally {
			close(rs, stmt, null, out);
		}
	}
	
	private static void _genBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType, String...tableNames) throws SQLException, ClassNotFoundException, IOException {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			dc.connection = getConnection(driverName, jdbcUrl, userName, password);
			dc.dbType = dbType;
			for(String tableName:tableNames) {
				_genBean(dc, fileRoot, packageName, tableName, false);
			}
		} finally {
			close(null, null, dc.connection, null);
		}
	}
	private static String getDriverName(DBType dbType) {
		if(dbType!=null) {
			switch (dbType) {
				case MySQL:
					return "com.mysql.jdbc.Driver";
				case MSSQLServer:
					return "net.sourceforge.jtds.jdbc.Driver";
			}
		}
		return null;
	}
	public static void genBean(JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName, String...tableName) throws SQLException, IOException, ClassNotFoundException {_genBean(getDriverName(jdbcUrl.getDbType()), jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType(), tableName);}
	
	private static List<String> getAllTables(DatabaseConnection databaseConnection, String jdbcUrl) throws SQLException {
		List<String> tables = new ArrayList<String>();
		if(databaseConnection!=null && databaseConnection.isOpen() && StringUtil.hasText(jdbcUrl)) {
			String selectAllTablesSQL = null;
			switch(databaseConnection.dbType) {
				case MySQL:
					selectAllTablesSQL = new StringBuilder("select table_name from information_schema.tables where table_schema = '").append(jdbcUrl.substring(jdbcUrl.lastIndexOf("/")+1).trim()).append("'").toString();
					break;
				case MSSQLServer:
					selectAllTablesSQL = "select table_name from information_schema.tables";
			}
			if(selectAllTablesSQL!=null) {
				PreparedStatement stmt = null;
				ResultSet rs = null;
				try {
					stmt = databaseConnection.connection.prepareStatement(selectAllTablesSQL);
					rs = stmt.executeQuery();
					while(rs.next()) {
						tables.add(rs.getString("table_name"));
					}
				} finally {
					close(rs, stmt, null, null);
				}
			}
		}
		return tables;
	}
	private static void _genBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType) throws SQLException, IOException, ClassNotFoundException {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			dc.connection = getConnection(driverName, jdbcUrl, userName, password);
			dc.dbType = dbType;
			for(String table:getAllTables(dc, jdbcUrl)) {
				_genBean(dc, fileRoot, packageName, table, false);
			}
		} finally {
			close(null, null, dc.connection, null);
		}
	}
	public static void genBean(JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName) throws SQLException, IOException, ClassNotFoundException {_genBean(getDriverName(jdbcUrl.getDbType()), jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType());}
	
	

	private static void _genJpaBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType, String...tableNames) throws SQLException, IOException, ClassNotFoundException {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			dc.connection = getConnection(driverName, jdbcUrl, userName, password);
			dc.dbType = dbType;
			for(String tableName:tableNames) {
				_genBean(dc, fileRoot, packageName, tableName, true);
			}
		} finally {
			close(null, null, dc.connection, null);
		}
	}
	public static void genJpaBean(JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName, String...tableName) throws SQLException, IOException, ClassNotFoundException {_genJpaBean(getDriverName(jdbcUrl.getDbType()), jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType(), tableName);}
	
	private static void _genJpaBean(String driverName, String jdbcUrl, String userName, String password, String fileRoot, String packageName, DBType dbType) throws SQLException, IOException, ClassNotFoundException {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			dc.connection = getConnection(driverName, jdbcUrl, userName, password);
			dc.dbType = dbType;
			for(String table:getAllTables(dc, jdbcUrl)) {
				_genBean(dc, fileRoot, packageName, table, true);
			}
		} finally {
			close(null, null, dc.connection, null);
		}
	}
	public static void genJpaBean(JdbcUrl jdbcUrl, String userName, String password, String fileRoot, String packageName) throws SQLException, IOException, ClassNotFoundException {_genJpaBean(getDriverName(jdbcUrl.getDbType()), jdbcUrl.getJdbcUrl(), userName, password, fileRoot, packageName, jdbcUrl.getDbType());}
	
	public static void main(String[] args) {
		try {
			BeanGenerator.genBean(new JdbcUrl(DBType.MySQL, "pc033", "HORSE_RACE"), "sa", "@satku74123698@", "c:/", "ebti.com.studentdata");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
